-- 课程发布情况带完成人数20230101-20230619
select  itm_id,
(select LISTAGG(USG_DISPLAY_BIL,'/') WITHIN GROUP(ORDER BY ERN_ORDER) as groupName
		from ENTITYRELATION,usergroup  where usg_ent_id=ERN_ANCESTOR_ENT_ID and
		ERN_CHILD_ENT_ID = SUBSTR(ITM_CREATE_USR_ID,4) and ERN_TYPE='USR_PARENT_USG'  and ERN_ORDER>1  ) 制作发布部门,itm_title 标题,
		(case when vcount>0 and dcount>0 then '文档、视频' when vcount>0 then '视频' when dcount>0 then '文档' else '--' end) 课程形式,
appCount 报名人数,comp_count 完成人数,
tr1.tnd_title 当前目录,
REGEXP_REPLACE(a.tnd_path, '^>', '') as 课程目录
from aeTreeNode tr1
inner join aeTreeNodeRelation on tr1.tnd_id = tnr_ancestor_tnd_id and
tnr_parent_ind = 1
inner join aeTreeNode tr2 on tr2.tnd_id = tnr_child_tnd_id
inner join aeItem on itm_id = tr2.tnd_itm_id and itm_run_ind != 1 and ITM_EXAM_IND!=1
INNER JOIN tctrainingcenter ON tcr_id = itm_tcr_id
INNER JOIN (
SELECT app_itm_id app_itm_id, COUNT(1) appCount FROM aeapplication WHERE app_status = 'Admitted' GROUP BY app_itm_id) tapp ON tapp.app_itm_id = itm_id
INNER JOIN (
SELECT itm_id itmId,COUNT(1) comp_count FROM aeitem
INNER JOIN course ON itm_Id = cos_itm_id
INNER JOIN courseevaluation ON cov_cos_id = cos_res_id WHERE cov_status = 'C' GROUP by itm_id) tem ON tem.itmId = itm_id
INNER JOIN ( SELECT itm_id itmId,sum(case when res_subtype='VOD' then 1 else 0 end) vcount
,sum(case when res_subtype!='VOD' then 1 else 0 end) dcount
FROM aeitem
INNER JOIN course ON itm_Id = cos_itm_id 
INNER JOIN ResourceContent ON rcn_res_id = cos_res_id
 inner join Resources on res_id = rcn_res_id_content
 group by itm_id ) tem ON tem.itmId = itm_id
 INNER JOIN(SELECT DISTINCT tr3.tnd_id,tr3.tnd_title,sys_connect_by_path(tr3.tnd_title,'>') as tnd_path from AETREENODE tr3 start with tr3.TND_PARENT_TND_ID is null connect by prior tr3.TND_ID=tr3.TND_PARENT_TND_ID ORDER BY tr3.tnd_id) a on a.TND_ID = tr1.tnd_id
where tnr_type = 'ITEM_PARENT_TND'
AND ITM_PUBLISH_TIMESTAMP >= to_date('2023-01-01 00:00:00','yyyy-mm-dd HH24:mi:ss')
AND ITM_PUBLISH_TIMESTAMP < to_date('2023-06-19 00:00:00','yyyy-mm-dd HH24:mi:ss');
 

 -- 考试发布情况带完成人数20230101-20230619
 select  itm_id,
tcr_title  发布单位,itm_title 标题,
appCount 报名人数,comp_count 完成人数, 
tr1.tnd_title 当前目录,
REGEXP_REPLACE(a.tnd_path, '^>', '') as 课程目录
 from aeTreeNode tr1
inner join aeTreeNodeRelation on tr1.tnd_id = tnr_ancestor_tnd_id and
tnr_parent_ind = 1
inner join aeTreeNode tr2 on tr2.tnd_id = tnr_child_tnd_id
inner join aeItem on itm_id = tr2.tnd_itm_id and itm_exam_ind  = 1
INNER JOIN tctrainingcenter ON tcr_id = itm_tcr_id
INNER JOIN (
SELECT app_itm_id app_itm_id, COUNT(1) appCount FROM aeapplication WHERE app_status = 'Admitted' GROUP BY app_itm_id) tapp ON tapp.app_itm_id = itm_id
INNER JOIN (
SELECT itm_id itmId,COUNT(1) comp_count FROM aeitem
INNER JOIN course ON itm_Id = cos_itm_id
INNER JOIN courseevaluation ON cov_cos_id = cos_res_id WHERE cov_status = 'C' GROUP by itm_id) tem ON tem.itmId = itm_id
INNER JOIN(SELECT DISTINCT tr3.tnd_id,tr3.tnd_title,sys_connect_by_path(tr3.tnd_title,'>') as tnd_path from AETREENODE tr3 start with tr3.TND_PARENT_TND_ID is null connect by prior tr3.TND_ID=tr3.TND_PARENT_TND_ID ORDER BY tr3.tnd_id) a on a.TND_ID = tr1.tnd_id
where tnr_type = 'ITEM_PARENT_TND'
AND ITM_PUBLISH_TIMESTAMP>= to_date('2023-01-01 00:00:00','yyyy-mm-dd HH24:mi:ss')
AND ITM_PUBLISH_TIMESTAMP < to_date('2023-06-19 00:00:00','yyyy-mm-dd HH24:mi:ss');